/*
 * bd.java
 *
 */

package dbUtil;
import java.sql.*;
import java.util.*;
import javax.servlet.*;

import javax.servlet.http.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;


/**
 *
 * @author
 */

public class BD {
    static ServletContext cfg;    
    
    // Valores constantes de operadores
    public static int AND_op = 0;
    public static int OR_op = 1;
    public static int COMA = 2;
    
    public static void setContext(ServletContext servlet_context)
    {
        cfg = servlet_context;
    }
    public synchronized static Connection getCon(DBConfig dc) throws Exception
    {   
        Connection conn = null;
        Class.forName("org.postgresql.Driver");
	Properties prop = new Properties();
        
        prop.setProperty("user", dc.getUser());
        prop.setProperty("password", dc.getPass());
                                
        DataSource ds = null;
       
        try {
            InitialContext cxt = new InitialContext();
            
            ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/postgres");
            
            conn = ds.getConnection();
            
            //conn = DriverManager.getConnection("jdbc:postgresql://" + dc.getServer() + "/" + dc.getDbname(), prop);
            
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return conn;
        //return DriverManager.getConnection(
               //"jdbc:postgresql://" + dc.getServer() + "/" + dc.getDbname(), prop);
    }
    
    /** Crea una sentencia where, que recibe 3 par�metros <p>
        hash = mapeamiento de key-value <p>
        match = puede ser 'LIKE' o '=' <p> 
        modifier = el modificador, en el caso de una busqueda like es el '%'
     */
    private static String priv_createWhere (Hashtable hash, String match, String modifier, int op, String prefix)
    {
        if (prefix == null)
        {
            prefix = "";
        }
        else
        {
            prefix = prefix +".";
        }
        Enumeration iter = hash.keys();
        String where_stm = "";
        String str_op ;
        
        if (op == AND_op)
        {
            str_op = " AND ";
        }
        else if (op == OR_op)
        {
            str_op = " OR ";
        }
        else if (op == COMA)
        {
            str_op = " , ";
        }
        else
        {
            str_op = " AND ";
        }
            
        while(iter.hasMoreElements())
        {
            String key = (String)iter.nextElement();
            //key = "lower(" + key + ")";
            Object valor = hash.get(key);
            
                if (where_stm.length() > 0) 
                {
                    where_stm += str_op; 
                }   
                String str_valor = "";
                if (valor instanceof Integer)
                {
                    Integer int_valor = (Integer)valor;
                    str_valor = int_valor.toString();
                }
                else
                {
                    str_valor = (String) valor;
                }
                if (key.startsWith("id") && modifier.equals("%"))
                    key = "to_char(" + key + ", '9999')";
                //Caso especifico para movilidad del tipo binary
                if (key.startsWith("movil") && modifier.equals("%")){
                    modifier = "";
                    match = "=";
                }
                
                where_stm += " " + prefix + key + " " + match + " '" + modifier + valor + modifier + "'";     
                
        }        
        return where_stm;
        
    }  
    
    public static ResultSet ejecutarConsulta( Connection con, String sql_query) throws SQLException{
        ResultSet rs_datos = null;
        
        Statement stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                                            ResultSet.CONCUR_READ_ONLY);
        
        // Se ejecuta la consulta y se recupera el ResultSet        
        
        rs_datos = stm.executeQuery(sql_query);               
                    
        return rs_datos;        
    }

    public static String createWhereStm(Hashtable hash, int op)
    {                  
        String stm = priv_createWhere(hash, "=", "", op, null);
        return stm;
    }    
    public static String createWhereLikeStm(Hashtable hash, int op)
    {
        String stm = priv_createWhere(hash, "LIKE", "%",op, null);
        return stm;
    }
    public static String createWhereLikeStmWithPrefix(Hashtable hash, int op, String prefix)
    {
        String stm = priv_createWhere(hash, "LIKE", "%",op, prefix);
        return stm;        
    }
    public static String createWhereStmWithPrefix(Hashtable hash, int op, String prefix)
    {
        String stm = priv_createWhere(hash, "=", "",op, prefix);
        return stm;        
    }
    public static String createSetStm(Hashtable hash)
    {
        String stm = priv_createWhere(hash, "=", "", COMA, null);
        return stm;        
    }
}